﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CommonLibrary.DB;
using System.Data;
using CommonLibrary.DataModel.Form;

namespace ServiceApplication.Dao.Form
{
    public class FormDao
    {
        /// <summary>
        /// 返回刚插入的主键
        /// </summary>
        /// <param name="formEntity"></param>
        /// <returns></returns>
        public int AddEntity(FormEntity formEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@name", formEntity.name));
            parameterList.Add(new SqlParameter("@catalogy", formEntity.catalogy));
            parameterList.Add(new SqlParameter("@dept_id", formEntity.dept_id));
            parameterList.Add(new SqlParameter("@user_id", formEntity.user_id));
            parameterList.Add(new SqlParameter("@user_name", formEntity.user_name));
            parameterList.Add(new SqlParameter("@location", formEntity.location));
            parameterList.Add(new SqlParameter("@pos", formEntity.pos));
            parameterList.Add(new SqlParameter("@createtime", formEntity.createtime));
            parameterList.Add(new SqlParameter("@lasttime", formEntity.lasttime));
            parameterList.Add(new SqlParameter("@descn", formEntity.descn));
            string sql = "insert into wf_form (name,catalogy,dept_id,user_id,user_name,pos,createtime,lasttime,descn,location) " +
                " values (@name,@catalogy,@dept_id,@user_id,@user_name,@pos,@createtime,@lasttime,@descn,@location);select @@identity ";
            string id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(id);
        }

        public void DleteEntity(int form_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@form_id", form_id));
            string sql = "delete from wf_form where form_id=@form_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public FormEntity QueryEntity(int form_id)
        {
            FormEntity formEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@form_id", form_id));
            string sql = "select form_id,name,catalogy,dept_id,user_id,user_name,pos,createtime,lasttime,descn,location from wf_form where form_id=@form_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            formEntity = new FormEntity();
            formEntity.form_id = int.Parse(result[0][0]);
            formEntity.name = result[0][1];
            formEntity.catalogy = result[0][2];
            formEntity.dept_id = int.Parse(result[0][3]);
            formEntity.user_id = int.Parse(result[0][4]);
            formEntity.user_name = result[0][5];
            formEntity.pos = int.Parse(result[0][6]);
            formEntity.createtime =result[0][7];
            formEntity.lasttime = result[0][8];
            formEntity.descn = result[0][9];
            formEntity.location = result[0][10];
            return formEntity;
        }

        public void UpdateEntity(FormEntity formEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@form_id", formEntity.form_id));
            parameterList.Add(new SqlParameter("@name", formEntity.name));
            parameterList.Add(new SqlParameter("@catalogy", formEntity.catalogy));
            parameterList.Add(new SqlParameter("@dept_id", formEntity.dept_id));
            parameterList.Add(new SqlParameter("@user_id", formEntity.user_id));
            parameterList.Add(new SqlParameter("@user_name", formEntity.user_name));
            parameterList.Add(new SqlParameter("@pos", formEntity.pos));
            parameterList.Add(new SqlParameter("@createtime", formEntity.createtime));
            parameterList.Add(new SqlParameter("@lasttime", formEntity.lasttime));
            parameterList.Add(new SqlParameter("@descn", formEntity.descn));
            parameterList.Add(new SqlParameter("@location", formEntity.location));
            string sql = "update wf_form set name=@name,catalogy=@catalogy,dept_id=@dept_id,user_id=@user_id,user_name=@user_name,pos=@pos," +
                " createtime=@createtime,lasttime=@lasttime,descn=@descn,location=@location where form_id=@form_id)";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public DataTable QueryFormDataTable(int user_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", user_id));
            string sql = "select form_id 编码,name 表单名称,catalogy 分类,dept_id 部门编码,user_id 创建人编码,user_name 创建人,pos 顺序号,createtime 创建时间," +
                " lasttime 最后修改时间,descn 描述,location 文件位置 from wf_form ";
            if (user_id != -1)
            {
                sql += " where user_id=@user_id";
            }
            DataTable dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public FormEntity QueryFormEntityByWfinsId(string wfinsId)
        {
            FormEntity formEntity;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@wfinsId", wfinsId));
            string sql = " select form.form_id,form.name,form.catalogy,form.dept_id,form.user_id,form.user_name,form.pos,form.createtime,form.lasttime,form.descn,form.location " +
                               " from wf_form form,wf_ins ins,wf_wf wf " +
                               " where form.form_id=wf.form_id and wf.wf_id=ins.wf_id and uniqueid=@wfinsId ";
            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (null==result) return null;

            formEntity = new FormEntity();
            formEntity.form_id = int.Parse(result[0][0]);
            formEntity.name = result[0][1];
            formEntity.catalogy = result[0][2];
            formEntity.dept_id = int.Parse(result[0][3]);
            formEntity.user_id = int.Parse(result[0][4]);
            formEntity.user_name = result[0][5];
            formEntity.pos = int.Parse(result[0][6]);
            formEntity.createtime = result[0][7];
            formEntity.lasttime =result[0][8];
            formEntity.descn = result[0][9];
            formEntity.location = result[0][10];
            return formEntity;
        }

        /// <summary>
        /// 查询闲置的表单
        /// </summary>
        /// <returns></returns>
        public DataTable QueryFreeForm()
        {
            string sql = "select form_id 编码,name 名称 from wf_form where form_id not in (select wf_wf.form_id from wf_wf) ";
            DataTable dt = DBExecuteUtil.querySqlTable(sql);
            return dt;
        }

        public DataTable QueryAllForm()
        {
            string sql = "select form_id 编码,name 名称 from wf_form  ";
            DataTable dt = DBExecuteUtil.querySqlTable(sql);
            return dt;
        }
    }
}
